package org.example.lethear.repository;

import org.example.lethear.entity.Whisper;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 悄悄话数据访问接口
 */
@Repository
public interface WhisperRepository extends JpaRepository<Whisper, Long> {
    
    /**
     * 根据目标类型和目标值查找悄悄话
     */
    Page<Whisper> findByTargetTypeAndTargetValue(Integer targetType, String targetValue, Pageable pageable);
    
    /**
     * 根据目标类型、目标值和状态查找悄悄话
     */
    Page<Whisper> findByTargetTypeAndTargetValueAndStatus(Integer targetType, String targetValue, Integer status, Pageable pageable);
    
    /**
     * 根据发送者ID查找悄悄话
     */
    Page<Whisper> findBySenderId(Long senderId, Pageable pageable);
    
    /**
     * 根据发送者ID和状态查找悄悄话
     */
    Page<Whisper> findBySenderIdAndStatus(Long senderId, Integer status, Pageable pageable);
    
    /**
     * 根据匿名状态查找悄悄话
     */
    Page<Whisper> findByIsAnonymous(Integer isAnonymous, Pageable pageable);
    
    /**
     * 根据状态查找悄悄话
     */
    Page<Whisper> findByStatus(Integer status, Pageable pageable);
    
    /**
     * 查找发给某个用户的悄悄话
     */
    @Query("SELECT w FROM Whisper w WHERE w.targetType = 1 AND w.targetValue = :username AND w.status = 1 ORDER BY w.createTime DESC")
    Page<Whisper> findWhispersToUser(@Param("username") String username, Pageable pageable);
    
    /**
     * 查找某个暗号下的悄悄话
     */
    @Query("SELECT w FROM Whisper w WHERE w.targetType = 2 AND w.targetValue = :secretCode AND w.status = 1 ORDER BY w.createTime DESC")
    Page<Whisper> findWhispersToSecretCode(@Param("secretCode") String secretCode, Pageable pageable);
    
    /**
     * 搜索悄悄话内容
     */
    @Query("SELECT w FROM Whisper w WHERE w.status = 1 AND w.content LIKE %:keyword%")
    Page<Whisper> searchWhispers(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 查找指定时间段内的悄悄话
     */
    @Query("SELECT w FROM Whisper w WHERE w.status = 1 AND w.createTime BETWEEN :startTime AND :endTime")
    Page<Whisper> findWhispersByCreateTimeBetween(@Param("startTime") LocalDateTime startTime, 
                                                 @Param("endTime") LocalDateTime endTime, 
                                                 Pageable pageable);
    
    /**
     * 查找最新悄悄话
     */
    @Query("SELECT w FROM Whisper w WHERE w.status = 1 ORDER BY w.createTime DESC")
    Page<Whisper> findRecentWhispers(Pageable pageable);
    
    /**
     * 查找匿名悄悄话
     */
    @Query("SELECT w FROM Whisper w WHERE w.isAnonymous = 1 AND w.status = 1 ORDER BY w.createTime DESC")
    Page<Whisper> findAnonymousWhispers(Pageable pageable);
    
    /**
     * 查找实名悄悄话
     */
    @Query("SELECT w FROM Whisper w WHERE w.isAnonymous = 0 AND w.status = 1 ORDER BY w.createTime DESC")
    Page<Whisper> findNamedWhispers(Pageable pageable);
    
    /**
     * 统计发给某个用户的悄悄话数量
     */
    @Query("SELECT COUNT(w) FROM Whisper w WHERE w.targetType = 1 AND w.targetValue = :username AND w.status = 1")
    long countWhispersToUser(@Param("username") String username);
    
    /**
     * 统计某个暗号的悄悄话数量
     */
    @Query("SELECT COUNT(w) FROM Whisper w WHERE w.targetType = 2 AND w.targetValue = :secretCode AND w.status = 1")
    long countWhispersToSecretCode(@Param("secretCode") String secretCode);
    
    /**
     * 统计某个用户发送的悄悄话数量
     */
    @Query("SELECT COUNT(w) FROM Whisper w WHERE w.senderId = :senderId AND w.status = 1")
    long countBySenderId(@Param("senderId") Long senderId);
    
    /**
     * 统计今日悄悄话数量
     */
    @Query("SELECT COUNT(w) FROM Whisper w WHERE w.createTime >= :startOfDay AND w.status = 1")
    long countTodayWhispers(@Param("startOfDay") LocalDateTime startOfDay);
    
    /**
     * 统计匿名悄悄话数量
     */
    @Query("SELECT COUNT(w) FROM Whisper w WHERE w.isAnonymous = 1 AND w.status = 1")
    long countAnonymousWhispers();
    
    /**
     * 查找用户今日发送的悄悄话数量（用于限制）
     */
    @Query("SELECT COUNT(w) FROM Whisper w WHERE w.senderId = :senderId AND w.createTime >= :startOfDay AND w.status = 1")
    long countTodayWhispersBySender(@Param("senderId") Long senderId, @Param("startOfDay") LocalDateTime startOfDay);
    
    /**
     * 查找需要审核的悄悄话
     */
    @Query("SELECT w FROM Whisper w WHERE w.status = 2 ORDER BY w.createTime ASC")
    Page<Whisper> findWhispersForReview(Pageable pageable);
    
    /**
     * 搜索特定名字或暗号的悄悄话
     */
    @Query("SELECT w FROM Whisper w WHERE w.status = 1 AND " +
           "((w.targetType = 1 AND w.targetValue LIKE %:searchTerm%) OR " +
           "(w.targetType = 2 AND w.targetValue LIKE %:searchTerm%)) " +
           "ORDER BY w.createTime DESC")
    Page<Whisper> searchWhispersByTargetValue(@Param("searchTerm") String searchTerm, Pageable pageable);
    
    /**
     * 精确搜索特定用户名的悄悄话
     */
    @Query("SELECT w FROM Whisper w WHERE w.targetType = 1 AND w.targetValue = :username AND w.status = 1 ORDER BY w.createTime DESC")
    Page<Whisper> findWhispersToSpecificUser(@Param("username") String username, Pageable pageable);
    
    /**
     * 精确搜索特定暗号的悄悄话
     */
    @Query("SELECT w FROM Whisper w WHERE w.targetType = 2 AND w.targetValue = :secretCode AND w.status = 1 ORDER BY w.createTime DESC")
    Page<Whisper> findWhispersToSpecificCode(@Param("secretCode") String secretCode, Pageable pageable);
    
    /**
     * 组合搜索：按目标值和内容关键词搜索
     */
    @Query("SELECT w FROM Whisper w WHERE w.status = 1 AND " +
           "((w.targetType = 1 AND w.targetValue LIKE %:targetValue%) OR " +
           "(w.targetType = 2 AND w.targetValue LIKE %:targetValue%)) AND " +
           "w.content LIKE %:contentKeyword% " +
           "ORDER BY w.createTime DESC")
    Page<Whisper> searchWhispersByTargetAndContent(@Param("targetValue") String targetValue, 
                                                   @Param("contentKeyword") String contentKeyword, 
                                                   Pageable pageable);
    
    /**
     * 根据状态统计悄悄话数量
     */
    long countByStatus(Integer status);
} 